﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace FluentETL.Impl
{
    public abstract class SqlBase : IFieldNames
    {
        protected SqlConnection ConnectionObject { get; set; }
        protected string ConnectionString { get; set; }
        protected CommandType CommandType { get; set; }
        protected string CommandText { get; set; }

        public virtual IEnumerable<string> GetFieldNames()
        {
            IList<string> fieldNames = new List<string>();
            WithConnection(conn =>
            {
                SqlCommand cmd = GetSelectCommand(conn);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
                DataTable schemaTable = rdr.GetSchemaTable();
                foreach (DataRow dr in schemaTable.Rows)
                {
                    fieldNames.Add((string)dr["BaseColumnName"]);
                }
            });
            return fieldNames;
        }

        protected SqlCommand GetSelectCommand(SqlConnection conn)
        {
            SqlCommand cmd;
            switch (this.CommandType)
            {
                case CommandType.Text:
                    cmd = new SqlCommand(this.CommandText, conn);
                    break;
                case CommandType.StoredProcedure:
                    cmd = new SqlCommand(this.CommandText, conn);
                    break;
                default:
                    cmd = new SqlCommand("SELECT * FROM " + DbUtil.SqlServerEscapeTableOrColumnName(CommandText), conn);
                    break;
            }
            return cmd;
        }

        protected void WithConnection(Action<SqlConnection> action)
        {
            if (this.ConnectionObject != null)
            {
                bool doOpen = this.ConnectionObject.State == ConnectionState.Closed;
                if (doOpen)
                    this.ConnectionObject.Open();
                action(this.ConnectionObject);
                if (doOpen)
                    this.ConnectionObject.Close();
            }
            else if (this.ConnectionString != null)
            {
                using (SqlConnection conn = new SqlConnection(this.ConnectionString))
                {
                    conn.Open();
                    action(conn);
                }
            }
            else
                throw new InvalidOperationException("No connection set.");
        }

        protected IEnumerable<T> WithConnectionEnumerate<T>(Func<SqlConnection, IEnumerable<T>> func)
        {
            if (this.ConnectionObject != null)
            {
                bool doOpen = this.ConnectionObject.State == ConnectionState.Closed;
                if (doOpen)
                    this.ConnectionObject.Open();
                foreach (T item in func(this.ConnectionObject))
                    yield return item;
                if (doOpen)
                    this.ConnectionObject.Close();
            }
            else if (this.ConnectionString != null)
            {
                using (SqlConnection conn = new SqlConnection(this.ConnectionString))
                {
                    conn.Open();
                    foreach (T item in func(conn))
                        yield return item;
                }
            }
            else
                throw new InvalidOperationException("No connection set.");
        }
    }
}
